package cn.edu.shou.lab.config.utils;

import cn.edu.shou.lab.config.query.Condition;
import cn.edu.shou.lab.config.query.Term;
import org.springframework.data.domain.Pageable;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.logging.Logger;

public class AdvancedSqlBuilder {
    private StringBuilder sb;
    private String dataTable;
    private Term term;
    private List<String> existTable = new ArrayList<>();

    private final static Logger LOGGER = Logger.getLogger(AdvancedSqlBuilder.class.getName());

    public AdvancedSqlBuilder(String dataTable, Term term, Pageable pageable){
        term.setPageable(pageable);
        this.dataTable = dataTable.toLowerCase();
        this.term = term;
    }

    public String getSql(){
        existTable.clear();
        sb = new StringBuilder("select * from " + dataTable);
        existTable.add(dataTable);
        setRelation();
        setCondition();
        setMySQLOrder();
        setPageableMySQL();
        System.out.println("Query : " + sb.toString());
        return sb.toString();
    }

    public String getCount(){
        existTable.clear();
        sb = new StringBuilder("select count(*) as count from " + dataTable);
        existTable.add(dataTable);
        setRelation();
        setCondition();
        System.out.println("Count : " + sb.toString());
        return sb.toString();
    }

    /**
     * 设置连接查询的关联关系
     */
    private void setRelation(){
        if(term.getRelations() == null){
            return;
        }
        for(Map<String , String> map : term.getRelations()){
            List<String> equalRelation = new ArrayList<>();
            for(Map.Entry<String, String> entry : map.entrySet()){
                String table = entry.getKey().trim().toLowerCase();
                equalRelation.add(entry.getKey() + "." + entry.getValue());
                if(!existTable.contains(table)){
                    sb.append(" left join " + table);
                    existTable.add(table);
                }
            }
            sb.append(" on " + equalRelation.get(0) + "=" + equalRelation.get(1));
        }
    }

    /*
    private void setCondition(){
        if(term.getConditions() == null){
            return;
        }
        sb.append(" where 1=1 ");
        for (Condition condition : term.getConditions()){
            sb.append(" and " + condition.getDataTable() + "." + condition.getName());
            int mode;
            try {
                mode = Integer.parseInt(condition.getMode());
                if(mode <= 0){
                    sb.append("='" + condition.getValue() + "' ");
                }else if(mode == 1){
                    sb.append(" like '" + condition.getValue() + "%' ");
                }else if(mode == 2){
                    sb.append(" like '%" + condition.getValue() + "' ");
                }else {
                    sb.append(" like '%" + condition.getValue() + "%' ");
                }
            }catch (NumberFormatException e){
                System.out.println(FastUtils.hightLightLog(e.getMessage()));
            }catch (Exception e){
                LOGGER.warning(e.getMessage());
            }
        }
    }
    */


    private void setCondition(){
        if(term.getConditions() == null){
            return;
        }
        sb.append(" where 1=1 ");

        try {
            for (Condition condition : term.getConditions()){
                Object value = condition.getValue();
                if(value instanceof ArrayList){
                    List<String> values = (ArrayList<String>)value;
                    String mode = Integer.toBinaryString(Integer.parseInt(condition.getMode()));
                    sb.append(" and " + condition.getDataTable() + "." + condition.getName());
                    if(mode.charAt(0) == '0'){
                        sb.append(">'" + values.get(0) + "' ");
                    }else if(mode.charAt(0) == '1'){
                        sb.append(">='" + values.get(0) + "' ");
                    }else{
                        throw new RuntimeException("查询条件 参数异常");
                    }
                    sb.append(" and " + condition.getDataTable() + "." + condition.getName());
                    if(mode.charAt(1) == '0'){
                        sb.append("<'" + values.get(1) + "' ");
                    }else if(mode.charAt(1) == '1'){
                        sb.append("<='" + values.get(1) + "' ");
                    }else{
                        throw new RuntimeException("查询条件 参数异常");
                    }
                }else if(value instanceof String || value instanceof char[]){
                    sb.append(" and " + condition.getDataTable() + "." + condition.getName());
                    int mode;
                    mode = Integer.parseInt(condition.getMode());
                    if(mode == -1){
                        sb.append("<>'" + value + "' ");
                    }else if(mode == 0){
                        sb.append("='" + value + "' ");
                    }else if(mode == 1){
                        sb.append(" like '" + value + "%' ");
                    }else if(mode == 2){
                        sb.append(" like '%" + value + "' ");
                    }else if(mode == 3){
                        sb.append(" like '%" + value + "%' ");
                    }else{
                        throw new RuntimeException("查询条件 参数异常");
                    }
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    private void setPageableMySQL(){
        if(term.getPageable() == null){
            return;
        }
        int left = term.getPageable().getPageSize() * (term.getPageable().getPageNumber() - 1);
        int right = term.getPageable().getPageSize();
        sb.append(" limit " + left + "," + right);
    }

//    private void setPageableSqlServer(){
//        if(term.getPageable() == null){
//            return;
//        }
//        int left = term.getPageable().getPageSize() * (term.getPageable().getPageNumber() - 1);
//        sb.append(" offset " + left + " rows fetch next " + term.getPageable().getPageSize() + " rows only ");
//    }

    private void setSqlServerOrder(){
        String dataTable = term.getOrder().getDataTable();
        String field = term.getOrder().getField();
        if(term.getOrder() == null || (dataTable == null && field == null)){
            sb.append(" order by id ");
            return;
        }else{
            if(dataTable == null || dataTable.isEmpty()){
                sb.append(" order by " + term.getOrder().getField());
            }else {
                sb.append(" order by " + term.getOrder().getDataTable() + "." + term.getOrder().getField());
            }
            if(!term.getOrder().isMode()){
                sb.append(" desc ");
            }
        }

    }

    private void setMySQLOrder(){
        if(term.getOrder() == null){
            return;
        }
        String dataTable = term.getOrder().getDataTable();
        String field = term.getOrder().getField();
        if(term.getOrder() == null || (dataTable == null && field == null)){
            return;
        }else{
            if(dataTable == null || dataTable.isEmpty()){
                sb.append(" order by " + term.getOrder().getField());
            }else {
                sb.append(" order by " + term.getOrder().getDataTable() + "." + term.getOrder().getField());
            }
            if(!term.getOrder().isMode()){
                sb.append(" desc ");
            }
        }

    }
}
